﻿using System.Drawing;
using DimensionsHelper.Common.Contracts.Excel;
using DimensionsHelper.Common.Contracts.Excel.Options;
using DimensionsHelper.Common.Excel;

namespace DimensionsHelper.ExcelAddIn.Core.Sql;

/// <summary>
/// 记录表类
/// </summary>
public class RecordTable
{

    private readonly Dictionary<string, FieldRecord> _fields = new();
    private readonly Dictionary<string, int> _values = new();

    private static readonly ExcelStyleColor DefaultInteriorColor = Color.LightGray.ToExcelColor();


    private static void SetBasicStyle(IExcelRangeOperator range, bool bold = false, bool italic = false, bool background = false)
    {
        range.SetStyle(new RangeStyleOptions
        {
            Font = new FontStyleOptions
            {
                FontType = (bold ? FontType.Bold : 0) | (italic ? FontType.Italic : 0)
            },
            InteriorColor = background ? DefaultInteriorColor : ExcelStyleColor.Empty
        });
    }


    private static void SetBasicBoldStyle(IExcelRangeOperator range)
    {
        SetBasicStyle(range, true, false, true);
    }


    /// <summary>
    /// 清除当前记录表中的所有数据。
    /// </summary>
    public void Clear()
    {
        _fields.Clear();
        _values.Clear();
    }


    /// <summary>
    /// 如果当前数据表没有有效数据，返回<see langword="true"/>。
    /// </summary>
    public bool IsEmpty => _fields.Count == 0 || _values.Count == 0;


    /// <summary>
    /// 获取已记录的字段名称数组。
    /// </summary>
    public string[] Fields => _fields.Keys.ToArray();


    /// <summary>
    /// 获取所有已记录的字段值数组。
    /// </summary>
    public string[] Values => _values.Keys.ToArray();


    /// <summary>
    /// 向当前记录表中添加一个字段、字段值和样本ID的记录。
    /// </summary>
    /// <param name="field">字段名</param>
    /// <param name="value">字段值</param>
    /// <param name="sampleId">样本ID</param>
    /// <returns></returns>
    public bool Add(string field, string value, string sampleId)
    {
        if (!_fields.TryGetValue(field, out FieldRecord fieldRecord))
        {
            fieldRecord = new FieldRecord(field);
            _fields.Add(field, fieldRecord);
        }

        if (_values.TryGetValue(value, out int c))
        {
            _values[value] = c + 1;
        }
        else
        {
            _values.Add(value, 1);
        }

        return fieldRecord.Add(value, sampleId);
    }

    /// <summary>
    /// 从当前记录表中删除一个字段、字段值和样本ID的记录。
    /// </summary>
    /// <param name="field">字段名</param>
    /// <param name="value">字段值</param>
    /// <param name="sampleId">样本ID</param>
    /// <returns></returns>
    public bool Remove(string field, string value, string sampleId)
    {
        if (!_fields.TryGetValue(field, out FieldRecord fieldRecord))
        {
            return false;
        }

        if (!_values.TryGetValue(value, out int count))
        {
            return fieldRecord.Remove(value, sampleId);
        }

        count--;

        if (count == 0)
        {
            _values.Remove(value);
        }
        else
        {
            _values[value] = count;
        }

        return fieldRecord.Remove(value, sampleId);
    }

    /// <summary>
    /// 将当前记录表写入Excel工作表。
    /// </summary>
    /// <param name="worksheet">指定工作表</param>
    /// <param name="weightingMap">样本权值表</param>
    /// <param name="decimals">保留的小数位数</param>
    /// <param name="addPercentage">是否显示百分比</param>
    public void WriteToExcel(IExcelWorksheetOperator worksheet,
                             Dictionary<string, double>? weightingMap = null,
                             int decimals = 0,
                             bool addPercentage = false)
    {
        // |       | 字段1 | 字段2 | 字段3  | 
        // | Base  |  ... | ...   | ...   |
        // | Val1  |  ... | ...   | ...   |
        // [ Per1  |  ... | ...   | ...   ]
        // ...
        // | ValN  |  ... | ...   | ...   |
        // [ PerN  |  ... | ...   | ...   ]
        // | Total |  ... | ...   | ...   |
        // [ PerT  |  ... | ...   | ...   ]

        string countFormat = weightingMap == null ? "" : $"0{(decimals > 0 ? $".{new string('0', decimals)}" : "")}";
        string percentFormat = $"0{(decimals > 0 ? $".{new string('0', decimals)}" : "")}%";

        // 1. 获取字段名称
        string[] fieldNames = Fields;
        int lastColumn = fieldNames.Length + 1;
        var headerRange = worksheet.GetRange(1, 2, 1, lastColumn);
        headerRange.Value = fieldNames.ToExcelRow();
        SetBasicBoldStyle(headerRange);
        // 2. 获取字段值
        string[] values = Values;
        Array.Sort(values);
        string[] side = values;
        // 3. 如果出示百分比，则在字段值后面添加一个空行
        if (addPercentage)
        {
            side = new string[values.Length * 2];
            for (int i = 0; i < values.Length; i++)
            {
                side[i * 2] = values[i];
                side[i * 2 + 1] = "%";
            }
        }

        var sideRange = worksheet.GetRange(3, 1, side.Length + 2, 1);
        sideRange.Value = side.ToExcelColumn();

        // 4. 写入基数描述
        var baseDescriptionCell = worksheet.GetRange(2, 1);
        baseDescriptionCell.Value = "Base";
        SetBasicBoldStyle(baseDescriptionCell);

        // 5. 写入基数值
        int[] baseValues = fieldNames.Select(f => _fields[f].Base).ToArray();
        IExcelRangeOperator baseValueRange = worksheet.GetRange(2, 2, 2, lastColumn);
        baseValueRange.Value = baseValues.ToExcelRow();
        SetBasicStyle(baseValueRange, true, true, true);

        // 6. 按行写入字段值
        FieldRecord[] records = fieldNames.Select(f => _fields[f]).ToArray();
        // 记录合计值
        double[] totals = new double[fieldNames.Length];
        double[] totalPercents = new double[fieldNames.Length];
        // 字段值数据从第3行开始
        int row = 3;
        foreach (string value in values)
        {
            // 写入字段值
            double[] valueArray = records.Select(r => r.Count(value, weightingMap)).ToArray();
            var valueRange = worksheet.GetRange(row, 2, row, lastColumn);
            valueRange.Value = valueArray;
            valueRange.NumberFormatLocal = countFormat;

            // 写入合计值
            for (int j = 0; j < fieldNames.Length; j++)
            {
                totals[j] += valueArray[j];
            }

            row++;
            if (!addPercentage)
            {
                continue;
            }

            // 写入百分比
            double[] percents = valueArray.Select((v, index) => v / baseValues[index]).ToArray();
            var percentRange = worksheet.GetRange(row, 2, row, lastColumn);
            percentRange.Value = percents;
            percentRange.NumberFormatLocal = percentFormat;
            SetBasicStyle(percentRange, italic: true);

            // 写入合计百分比
            for (int j = 0; j < fieldNames.Length; j++)
            {
                totalPercents[j] += percents[j];
            }

            row++;
        }

        // 7. 写入合计描述
        IExcelRangeOperator totalDescriptionCell = worksheet.GetRange(row, 1);
        totalDescriptionCell.Value = "Total";
        SetBasicBoldStyle(totalDescriptionCell);

        // 8. 写入合计值
        var totalValueRange = worksheet.GetRange(row, 2, row, lastColumn);
        totalValueRange.Value = totals;
        totalValueRange.NumberFormatLocal = countFormat;
        SetBasicBoldStyle(totalValueRange);

        // 9. 写入合计百分比
        if (addPercentage)
        {
            row++;
            var totalPercentDescriptionCell = worksheet.GetRange(row, 1);
            totalPercentDescriptionCell.Value = "%";
            SetBasicStyle(totalPercentDescriptionCell, true, true, true);
            var totalPercentRange = worksheet.GetRange(row, 2, row, lastColumn);
            totalPercentRange.Value = totalPercents;
            totalPercentRange.NumberFormatLocal = percentFormat;
            SetBasicStyle(totalPercentRange, true, true, true);
        }

        // 10. 添加全局格式
        var tableRange = worksheet.GetRange(1, 1, row, lastColumn);
        // 边框
        tableRange.SetRangeBorderStyle(
            BorderPosition.Around | BorderPosition.Horizontal | BorderPosition.Vertical,
            BorderStyle.Thin);
        // 对齐
        tableRange.SetStyle(new RangeStyleOptions
        {
            HAlign = ExcelHAlign.Center,
            VAlign = ExcelVAlign.Center
        });
        // 首列左对齐
        worksheet.GetRange(1, 1, row, 1).SetStyle(new RangeStyleOptions
        {
            HAlign = ExcelHAlign.Left
        });
        // 设置列宽
        worksheet.AutoFitEntireColumns();
    }

}
